ALTER INDEX
ALTER INDEX — Change the Definition of an Index
Synopsis
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name ATTACH PARTITION index_name
ALTER INDEX name DEPENDS ON EXTENSION extension_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number
SET STATISTICS integer
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]
Description
ALTER INDEX
Changes the definition of an existing index. Several sub-forms are described below. Note that the lock level required by each sub-form may be different. Unless explicitly stated, an ACCESS EXCLUSIVE lock is held. When multiple sub-commands are listed, the lock held will be the strictest one required by any sub-command.
RENAME
The RENAME form changes the name of the index. If the index is associated with a table constraint (UNIQUE, PRIMARY KEY, or EXCLUDE), the constraint will also be renamed. This has no effect on stored data. Renaming an index acquires a SHARE UPDATE EXCLUSIVE lock.
SET TABLESPACE
This form changes the tablespace of the index to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. The ALL IN TABLESPACE form can be used to move all indexes in one tablespace in the current database to another tablespace; this will lock all indexes to be moved and then move each one. This form also supports OWNED BY, which moves only indexes owned by the specified roles. If the NOWAIT option is specified, the command will fail if it cannot acquire all locks immediately. Note that this command does not move system catalogs;
if you want to move system catalogs, use ALTER DATABASE or explicit ALTER INDEX calls. See also CREATE TABLESPACE.
ATTACH PARTITION
Causes the named index to become attached to the altered index. The referenced index must be on a partition of the table containing the altered index and have an equivalent definition. An attached index cannot be dropped individually; it will be automatically dropped when its parent index is dropped.
DEPENDS ON EXTENSION extension_name
NO DEPENDS ON EXTENSION extension_name
This form marks the index as dependent on the extension, or if NO is specified, no longer dependent on that extension. Indexes marked as dependent on an extension are automatically dropped when the extension is dropped.
SET ( storage_parameter [= value] [, ... ] )
This form changes one or more index-method-specific storage parameters for the index. See CREATE INDEX for available parameters. Note that this command does not immediately modify the index contents; depending on the parameter, you may need to rebuild the index with REINDEX to get the desired effect.
RESET ( storage_parameter [, ... ] )
This form resets one or more index-method-specific storage parameters to their default values. As with SET, a REINDEX may be needed to fully update the index.
ALTER [ COLUMN ] column_number SET STATISTICS integer
This form sets the per-column statistics collection target for subsequent ANALYZE operations, but can only be used on index columns that are defined as expressions. Since expressions lack unique names, they are referenced by the ordinal number of the index column. The collection target can be set to a value in the range 0 to 10000. Alternatively, setting it to -1 reverts to using the system default statistics target (default_statistics_target).
Parameters
IF EXISTS
Do not throw an error if the index does not exist. A notice is issued in this case.
column_number
The ordinal number that refers to the ordinal (left-to-right) position of the index column.
name
The name (optionally schema-qualified) of an existing index to alter.
new_name
The new name for the index.
tablespace_name
The tablespace to which the index will be moved.
extension_name
The name of the extension on which the index depends.
storage_parameter
The name of an index-method-specific storage parameter.
value
The new value for an index-method-specific storage parameter. Depending on the parameter, this might be a number or a word.
Notes
These operations can also be performed using ALTER TABLE. In fact, ALTER INDEX is just an alias for the forms of ALTER TABLE that apply to indexes.
There used to be an ALTER INDEX OWNER variant, but it is now ignored (a warning is issued). An index's owner cannot be different from its base table's owner. Changing the base table's owner automatically changes the index's owner. Altering any part of a system catalog index is not permitted.
Examples
# To rename an existing index:
ALTER INDEX distributors RENAME TO suppliers;
# To move an index to a different tablespace:
ALTER INDEX distributors SET TABLESPACE fasttablespace;
# To change the fill factor of an index (assuming the index method supports fill factor):
ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;
# To set the statistics collection target for an expression index:
CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;